package com.nickmao.stumgr.android.db;


import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

import com.nickmao.stumgr.android.po.*;

public class StudentDatabaseHelper extends SQLiteOpenHelper {

	private final static int VERSION = 1;
	
	private final static String DATABASE = "stu_mgr";
	
	private final static String STUTABLE_STRING = "Student";
	
	private final String TAG = this.getClass().getSimpleName();
	
	public StudentDatabaseHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		// TODO Auto-generated constructor stub
	}
	
	public StudentDatabaseHelper(Context context,int version){
		super(context,DATABASE,null,version);
	}
	
	public StudentDatabaseHelper(Context context){
		super(context, DATABASE, null, VERSION);
	}
	
	public long addNewStudent(Student student){
		ContentValues values = new ContentValues();
		values.put("sno", student.getSno());
		values.put("sname", student.getSname());
		values.put("sex", student.getSex());
		values.put("birthday", student.getBirthday());
		values.put("email", student.getEmail());
		values.put("mobileno", student.getMobileno());
		
		SQLiteDatabase db = getWritableDatabase();
		return db.insert(STUTABLE_STRING, null, values);
	}
	
	public int notifyStudent(Student student){
		ContentValues values = new ContentValues();
		values.put("sname", student.getSname());
		values.put("sex", student.getSex());
		values.put("birthday", student.getBirthday());
		values.put("email", student.getEmail());
		values.put("mobileno", student.getMobileno());
		
		SQLiteDatabase db = getWritableDatabase();
		return db.update(STUTABLE_STRING, values, "sno=?", new String[]{student.getSno()});
	}
	
	public List<Student> getStudents(){
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.query(STUTABLE_STRING, new String[]{"sno","sname","sex","birthday","email","mobileno"}, null, null, null, null, null);
		List<Student> lst = new ArrayList<Student>();
		while(cursor.moveToNext()){
			Student s = new Student();
	    	s.setSno(cursor.getString(0));
	    	s.setSname(cursor.getString(1));
	    	s.setSex(cursor.getInt(2));
	    	s.setBirthday(cursor.getString(3));
	    	s.setEmail(cursor.getString(4));
	    	s.setMobileno(cursor.getString(5));
	    	lst.add(s);
		}
		cursor.close();
		return lst;
	}
	
	public boolean Exists(String sno){
		Cursor cursor = null;
		SQLiteDatabase db = getReadableDatabase();
		cursor = db.query(STUTABLE_STRING, new String[]{"sno","sname","sex","birthday","email","mobileno"}, "sno=?", new String[]{sno}, null, null, null);

        int cnt = cursor.getCount();
        cursor.close();
        return cnt>0;
	}
	
	public Student getStudentDetails(String sno){
		Cursor cursor = null;
		
		SQLiteDatabase db = getReadableDatabase();
		cursor = db.query(STUTABLE_STRING, new String[]{"sno","sname","sex","birthday","email","mobileno"}, "sno=?", new String[]{sno}, null, null, null);
        Student s = null;
        if(cursor.moveToNext()){
	        s = new Student();
	    	s.setSno(cursor.getString(0));
	    	s.setSname(cursor.getString(1));
	    	s.setSex(cursor.getInt(2));
	    	s.setBirthday(cursor.getString(3));
	    	s.setEmail(cursor.getString(4));
	    	s.setMobileno(cursor.getString(5));
        }
        cursor.close();
	    return s;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
	    Log.i(TAG, "CREATING NEW TABLES");
	    StringBuilder sb = new StringBuilder();
	    sb.append("CREATE TABLE ").append(STUTABLE_STRING)
	      .append(" (sno NVARCHAR(10),sname NVARCHAR(20),sex INTEGER,birthday VARCHAR(20),email VARCHAR(50),mobileno varchar(20))");
	    db.execSQL(sb.toString());
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.w(TAG, "Be Careful this method will destroy all the data");
	}
    
}
